﻿using MySql.Data.MySqlClient;
using NB.MGJ.Model;
using NB.MGJ.Model.AntiFleeingEntity;
using NB.MGJ.Model.Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NB.MGJ.Service
{
    /// <summary>
    /// 窜货
    /// </summary>
    /// =======================================================================
    /// 类名：DemoService DemoController的备注：DemoService~1<br/>
    /// =======================================================================<br/>
    /// 更新履历<br/>
    /// 序号          更新日期          责任人           更新内容<br/>
    /// 001           2017/08/30         华君             装箱<br/>
    /// 002           2017/08/31         华君             调拨<br/>
    /// 003           2017/08/31         华君             退货<br/>
    /// 004           2017/08/31         华君             处理状态<br/>
    /// 005           2017/09/01         华君             调货<br/>
    /// 006           2017/09/05         华君             出库<br/>
    /// 007           2017/09/06         华君             处理状态<br/>
    /// 008           2017/09/07         华君             窜货验证<br/>
    /// =======================================================================<br/>
    public partial class FleeGoodsService
    {
        #region 公用方法
        //根据物流码得到生码信息
        public AntiFleeing_PackBox GetGenCodeInfo(string code)
        {
            string sql = @" select Gen_Code_Nums GenCodeNum ,CASE CodeType when '1' THEN '防伪码' when '2' THEN '管理码' else '防伪码,管理码' end as CodeType,EnterpriseId from CodeManage_GenCode where EnterpriseId=( select ID from Organization_Enterprise where Code='" + code.Substring(0, 4) + "')" +
"  and PatchId= (select ID from CodeManage_GenCodeBatch where ViewFlag=1 and  EnterpriseId=( select ID from Organization_Enterprise where Code='" + code.Substring(0, 4) + "') and Batch=" + code.Substring(4, AppConst.SubBatchLen) + ")";
            return _baseRepository.ExecuteText<AntiFleeing_PackBox>(sql, null);
        }
        /// 根据物流码 查询是否存在
        public AntiFake_Digitcode GetTable(string code)
        {
            try
            {
                string tabSuffix = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);//获取到表名
                string sql = @"select * from {0}  where Wl_Code='{1}' limit 1";
                sql = string.Format(sql, tabSuffix, code);
                return _baseRepository.ExecuteText<AntiFake_Digitcode>(sql);
            }
            catch (Exception)
            {
                return null;
            }
        }
        //事物 改变parentCode
        public void littleUpload(Hashtable ht)
        {
            _baseRepository.ExecuteSqlTran(ht);
        }
        public int UpdateBoxCode(string boxCode, string singleCodes, string tableName)
        {
            string sql = "UPDATE " + tableName + " SET parentCode='" + boxCode + "' where  Wl_Code in (" + singleCodes + ")";
            return _baseRepository.ExecuteSql<int>(sql);
        }
        //根据物流码得到产品ID
        public T GetModel<T>(string wlCode)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + wlCode.Substring(0, AppConst.SubStrLen);
            string sql = @"SELECT CONVERT(PRODUCTID USING utf8) PRODUCTID FROM {0}  WHERE WL_CODE={1}";
            sql = string.Format(sql, tableName, wlCode);
            return _baseRepository.ExecuteText<T>(sql, null);
        }
        //显示详细数据的公共方法
        public T GetShowData<T>(int id, string tableName)
        {
            System.Type type = typeof(int);
            string sql = " SELECT * FROM " + tableName + " WHERE ID=@ID";
            MySqlParameter[] parameters = {
                            new MySqlParameter("@ID",id)
                             };
            return _baseRepository.ExecuteText<T>(sql, parameters);
        }
        //根据物流码查询
        public List<Product_Product> GetProcductService(string enterIds)
        {
            string sql = "SELECT ID,PRODUCTNAME FROM PRODUCT_PRODUCT WHERE VIEWDEL=0 ";
            if (enterIds != " getJuniorEnterprise(1)")
            {
                sql += " " + " AND ENTERPRISEID IN({0})";
                sql = string.Format(sql, enterIds);
            }
            return _baseRepository.ExecuteSqlCollection<Product_Product>(sql).ToList();
        }
        public List<Product_Product> GetProcductService(int userId)
        {
            string sql = "SELECT ID,PRODUCTNAME FROM PRODUCT_PRODUCT WHERE VIEWDEL=0 AND CREATELOGINID ={0}";
            sql = string.Format(sql, userId);
            return _baseRepository.ExecuteSqlCollection<Product_Product>(sql).ToList();
        }
        public List<AntiFleeing_SalesArea> GetSaleAreaService(string enterIds)
        {
            string sql = " SELECT ID,NAME FROM ANTIFLEEING_SALESAREA WHERE VIEWDEL=0 AND ENTERPRISEID IN ({0})";
            sql = string.Format(sql, enterIds);
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql).ToList();
        }
        public List<AntiFleeing_SalesArea> GetSaleAreaService()
        {
            string sql = " SELECT ID,NAME FROM ANTIFLEEING_SALESAREA WHERE VIEWDEL=0 ";

            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql).ToList();
        }
        public List<AntiFleeing_SalesArea> GetSaleAreaService(int dicId)
        {
            string sql = "SELECT ID,NAME FROM ANTIFLEEING_SALESAREA WHERE ID=(SELECT SALESAREAID FROM ANTIFLEEING_DISTRIBUTOR  WHERE ID=@ID)";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@ID",dicId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql, parameters).ToList();
        }
        /// <summary>
        /// 机构登陆时获取的经销商下拉信息
        /// </summary>
        /// <param name="saleAreaId"></param>
        /// <returns></returns>
        public List<AntiFleeing_Distributor> GetDistributorService(int saleAreaId, int disId = 0)
        {
            string sql = "  SELECT ID,NAME FROM ANTIFLEEING_DISTRIBUTOR WHERE VIEWDEL=0 AND SALESAREAID=" + saleAreaId + " and Id !=" + disId;
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql).ToList();
        }
        /// <summary>
        /// 经销商登录时获取的经销商下拉信息
        /// </summary>
        /// <param name="saleAreaId"></param>
        /// <param name="disId"></param>
        /// <returns></returns>
        public List<AntiFleeing_Distributor> GetDistributorService(int saleAreaId, int disId, int selfDisId = 0)
        {
            string sql = "  SELECT ID,NAME FROM ANTIFLEEING_DISTRIBUTOR WHERE VIEWDEL=0 AND SALESAREAID=@SALESAREAID AND FIND_IN_SET(ID,getDiscChild(@ID)) And ID!=@DisId";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@SALESAREAID",saleAreaId),
                                            new MySqlParameter("@ID",disId),
                                             new MySqlParameter("@DisId",selfDisId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql, parameters).ToList();
        }
        public List<AntiFleeing_Distributor> GetDistributorService_Disc(int saleAreaId, int selfDisId = 0)
        {
            string sql = "  SELECT ID,NAME FROM ANTIFLEEING_DISTRIBUTOR WHERE VIEWDEL=0 AND SALESAREAID=@SALESAREAID AND FIND_IN_SET(ID,getDiscChild(@ID)) And ID!=@DisId";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@SALESAREAID",saleAreaId),
                                            new MySqlParameter("@ID",selfDisId),
                                             new MySqlParameter("@DisId",selfDisId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql, parameters).ToList();
        }
        //根据物流码查询入库信息
        public AntiFleeing_IOWarehouse GetIOWarehouseInfo(string fw_code, int enterId)
        {
            string sql = "SELECT A.WAREHOUSEID,B.PRODUCTID,B.CREATELOGINID FROM ANTIFLEEING_WAREHOUSINGDETAILS A LEFT JOIN ANTIFLEEING_IOWAREHOUSE B ON A.FORMCODE=B.FORMCODE WHERE A.WLCODE='" + fw_code + "' AND B.ENTERPRISEID=" + enterId + "  ORDER BY A.CREATEDATE DESC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_IOWarehouse>(sql);
        }
        //判断机构是否存在入库信息
        //2017/11/10 huajun
        public bool GetInStorageInfo(string fw_code, int enterId)
        {
            bool IsHas = true;
            string sql = "select ID from sys_user where EnterpriseID=" + enterId + " and Distributor=0 and ID=(select userId from ANTIFLEEING_WAREHOUSINGDETAILS where WLCODE='" + fw_code + "' ORDER BY CREATEDATE DESC LIMIT 1)";
            Sys_User model = _baseRepository.ExecuteText<Sys_User>(sql);
            if (model != null) IsHas = false;
            return IsHas;
        }
        //得到最后一个入库明细
        public bool IsInStorage_Dist(string fw_code,int dId)
        {
            bool IsHas = true;
            string sql = "select ID from sys_user where Distributor=" + dId + " and ID=(select userId from ANTIFLEEING_WAREHOUSINGDETAILS where WLCODE='" + fw_code + "' ORDER BY CREATEDATE DESC LIMIT 1)";
            Sys_User model = _baseRepository.ExecuteText<Sys_User>(sql);
            if (model != null) IsHas = false;
            return IsHas;
        }
        //根据物流码和用户Id查询入库明细 判断是否重复入库
        public AntiFleeing_WareHousingDetails GetInStorageDetail(string fw_code,int uId)
        {
            string sql = "select * from ANTIFLEEING_WAREHOUSINGDETAILS where WLCODE='" + fw_code + "' and userId='" + uId + "'";
            AntiFleeing_WareHousingDetails model = _baseRepository.ExecuteText<AntiFleeing_WareHousingDetails>(sql);
            return model;
        }
        // 根据物流码查询入库明细
        public AntiFleeing_WareHousingDetails GetIntoTable(string fw_code, string uId)
        {
            string sql = @"select  a.ID,EnterpriseId,Distributor from (
select distinct EnterpriseId,ID from Sys_User where ID=" + uId + " and EnterpriseId in (select su.EnterpriseId from Sys_User su " +
 " left join AntiFleeing_WareHousingDetails aw on su.ID=aw.UserID where aw.WLcode='" + fw_code + "')) a " +
 " left join( select distinct Distributor,ID from Sys_User where ID=" + uId + " and Distributor in (select su.Distributor from Sys_User su " +
 " left join AntiFleeing_WareHousingDetails aw " +
 " on su.ID=aw.UserID where aw.WLcode='" + fw_code + "') ) b on a.ID=b.ID LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_WareHousingDetails>(sql);
        }
        //根据物流码和经销商ID查询经销商入库信息
        public bool GetIntoInfo(string fw_code, string uId)
        {
            bool IsHas = true;
            string sql = @"select UserID as ID from AntiFleeing_WareHousingDetails WHERE wlcode='" + fw_code + "' ORDER BY createDate DESC  LIMIT 1";
            var model = _baseRepository.ExecuteText<Sys_User>(sql);
            if (model == null || model.ID.ToString() != uId) IsHas = false;
            return IsHas;
        }
        //根据物流码和机构ID查询入库信息
        public AntiFleeing_Warehouse GetWarehouseInfo(string code, int eId = 0, int uId = 0)
        {
            string sql = "select * from antifleeing_warehouse where ChangeLoginID in ( {0} ) " +
    " and ID=(SELECT WarehouseId FROM `antifleeing_warehousingdetails` where WLcode='{1}' ORDER BY ID DESC  limit 1)";
            if (eId != 0) sql = string.Format(sql, " select Id from sys_user where distributor=0 and enterpriseId=" + eId, code);
            else sql = string.Format(sql, uId, code);
            return _baseRepository.ExecuteText<AntiFleeing_Warehouse>(sql);
        }

        //根据物流码查询出库信息
        public AntiFleeing_OutboundDetails GetOutInfo(string fw_code, int enterId)
        {
            string sql = "SELECT A.*,B.CHANGELOGINID FROM ANTIFLEEING_OUTBOUNDDETAILS A LEFT JOIN ANTIFLEEING_IOWAREHOUSE B ON A.FORMCODE=B.FORMCODE WHERE A.WLCODE='" + fw_code + "' AND B.ENTERPRISEID=" + enterId + " order by A.ID desc  limit 1";
            return _baseRepository.ExecuteText<AntiFleeing_OutboundDetails>(sql);
        }
        //根据物流码查询出库明细
        public AntiFleeing_OutboundDetails GetOutTable(string fw_code)
        {
            string sql = @"SELECT * FROM ANTIFLEEING_OUTBOUNDDETAILS  WHERE WLCODE='{0}' and DistributorID!=0 ORDER BY  CREATEDATE DESC LIMIT 1";
            sql = string.Format(sql, fw_code);
            return _baseRepository.ExecuteText<AntiFleeing_OutboundDetails>(sql);
        }
        //获取当前经销商的所有父节点 包括自己
        public List<AntiFleeing_Distributor> GetEnterAllParent(int Id)
        {
            string sql = "SELECT * FROM ANTIFLEEING_DISTRIBUTOR WHERE FIND_IN_SET(ID,GETDISCPARLST(" + Id + "))";
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql).ToList();
        }
        //获取经销商最高等级名称
        public AntiFleeing_DistributorLevel GetDistLevelModel(int eId)
        {
            string sql = "select * from antifleeing_distributorlevel where EnterpriseId=" + eId + " and ViewDel=0 ORDER BY  `Level` ASC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_DistributorLevel>(sql);
        }
        public AntiFleeing_Distributor GetDistributor(int Id)
        {
            string sql = "select a.id,a.`Name`,l.levelName from antifleeing_distributor a  left join antifleeing_distributorlevel l on a.levelid=l.id where a.id=" + Id; ;
            return _baseRepository.ExecuteText<AntiFleeing_Distributor>(sql);
        }

        // app 出入库批量插入和修改
        public bool ExecuteSqlTran(DataTable dt, int productId, string formCode, string tableName)
        {
            bool _Result = true;
            string connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
            string filePath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            int insertCount = 0;
            string tmpPath = filePath + DateTime.Now.ToString("yyMMddhhmmss") + ".csv";
            string csv = DataTableToCsv(dt);
            File.WriteAllText(tmpPath, csv);
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = conn.CreateCommand();
                    try
                    {
                        //1.批量导入
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = tmpPath,
                            NumberOfLinesToSkip = 0,
                            TableName = tableName,
                        };
                        bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                        insertCount = bulk.Load();
                        if (insertCount == dt.Rows.Count)
                        {
                            //2.批量修改
                            //cmd = new MySqlCommand("proc_getBatchUpdate", conn);
                            cmd.CommandText = "proc_getBatchUpdate";
                            //cmd.CommandTimeout =5000; //设置执行时间
                            cmd.CommandType = CommandType.StoredProcedure;
                            MySqlParameter[] paras = 
                         {
                          new MySqlParameter("@formCode",formCode),
                          new MySqlParameter("@tableName",dt.TableName),
                          new MySqlParameter("@productId",productId),
                          new MySqlParameter("@formTable",tableName),
                         };
                            if (paras != null)
                            {
                                foreach (MySqlParameter item in paras)
                                {
                                    cmd.Parameters.Add(item);
                                }
                            }
                            cmd.ExecuteNonQuery();
                            trans.Commit();

                        }
                        else
                        {
                            trans.Rollback();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        _Result = false;
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }
            File.Delete(tmpPath);
            return _Result;
        }

        //一般批量插入
        public void ExecuteOfBatch(DataTable dt, string tableName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
            string filePath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        //mysql批量插入
                        int insertCount = 0;
                        string tmpPath = filePath + DateTime.Now.ToString("yyMMddhhmmss") + ".csv";
                        string csv = DataTableToCsv(dt);
                        File.WriteAllText(tmpPath, csv);
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = tmpPath,
                            NumberOfLinesToSkip = 0,
                            TableName = tableName,
                        };
                        bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                        insertCount = bulk.Load();
                        File.Delete(tmpPath);
                        trans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        ///将DataTable转换为标准的CSV
        /// </summary>
        /// <param name="table">数据表</param>
        /// <returns>返回标准的CSV</returns>
        private static string DataTableToCsv(DataTable table)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0) sb.Append(",");
                    if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                    {
                        sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else sb.Append(row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        public CodeManage_Allot CodeAllotInfo(int formEId, int goEId, string batch)
        {
            string sql = "select * from codemanage_allot where F_EnterpriseId={0} and T_EnterpriseId={1} and AllotBatch={2}";
            return _baseRepository.ExecuteText<CodeManage_Allot>(string.Format(sql, formEId, goEId, batch));
        }

        #endregion

        #region 装箱
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int PackBoxAdd(AntiFleeing_PackBox model)
        {
            return _baseRepository.InsertReturnID<AntiFleeing_PackBox>(model);
        }
        public AntiFleeing_PackBox PackBoxInfo(int eId, string bacth)
        {
            string sql = "SELECT * from antifleeing_packbox where  ViewDel=0 and EnterpriseID={0} and Bacth={1}";
            return _baseRepository.ExecuteText<AntiFleeing_PackBox>(string.Format(sql, eId, bacth));

        }
        /// <summary>
        /// 根据ID得到model
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AntiFleeing_PackBox PackBoxGetModel(string id)
        {
            string sql = " SELECT * FROM ANTIFLEEING_PACKBOX WHERE  ViewDel=0 and ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<AntiFleeing_PackBox>(sql, parameters);
        }
        /// <summary>
        /// 根据FormCode得到一个model
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public AntiFleeing_PackBox PBGetModelByCode(string code)
        {
            string sql = @"SELECT * FROM ANTIFLEEING_PACKBOX WHERE ViewDel=0 and FORMCODE LIKE '" + code + "%'  ORDER BY CREATEDATE DESC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_PackBox>(sql, null);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int PackBoxUpdate(AntiFleeing_PackBox model)
        {
            return _baseRepository.Update<AntiFleeing_PackBox>(model);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public PagedList<V_PackBox> PackBoxGetPageList(ref PagerModel pager, SearchModel SearchModel)
        {
            DataTable result = new DataTable();

            pager.FldName = @" * ";
            pager.TblName = @" V_PackBox as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "  1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }

            if (SearchModel != null)
            {
                StrCondition += " and  (w.FormCode like '%" + SearchModel.Key + "%') and (w.FormCode like '%" + SearchModel.Code + "%') and (concat_ws('',w.CodeType) like '%" + SearchModel.CodeType + "%') and (concat_ws('',w.ScalingRelation) like '%" + SearchModel.ScalingRelation + "%') and ( concat_ws('',w.statusText) like '%" + SearchModel.ImportStatus + "%') ";
            }
            if (SearchModel.StartDate != "" && SearchModel.StartDate != null)
            {
                StrCondition += " and  (left(CreateDate,10) >= '" + SearchModel.StartDate + "')";
            }
            if (SearchModel.EndDate != "" && SearchModel.EndDate != null)
            {
                StrCondition += " and  (left(CreateDate,10) <= '" + SearchModel.EndDate + "')";
            }

            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = "    w.CreateDate desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);

            IEnumerable<V_PackBox> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_PackBox>("Common_PageList", parameters);
            return new PagedList<V_PackBox>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));

        }
        /// <summary>
        /// 装箱编码
        /// </summary>
        /// <param name="id">机构ID</param>
        /// <returns></returns>
        public string PackBoxGetCode(string id)
        {
            string Code = GetEnterprise(id).Code;
            if (Code.Length < 4)//如果小于4 用0补充
            {
                for (int i = 0; i < 4 - Code.Length; i++)
                {
                    Code += "0";
                }
            }
            //  else Code = Code.Substring(0, 4);

            //判断单据号是否存在
            AntiFleeing_PackBox model = PBGetModelByCode("ZX" + Code + "0001");
            string str = "";
            if (model == null)//不存在 
            {
                // Code += "0001";
                return "ZX" + Code + "0001";
            }
            else
            {
                str = model.FormCode.Substring(model.FormCode.Length - 4, 4);
                int num = int.Parse(str);
                num++;
                str = num.ToString();
                if (str.Length < 4)//如果小于4 用0补充
                {

                    str = str.PadLeft(4, '0');
                    //str= str.PadLeft(4-str.Length,'0');
                }
                else str = str.Substring(0, 4);

            }
            Code = "ZX" + Code + str;
            return Code;
        }

        #endregion

        #region 产品调拨
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AllocationAdd(AntiFleeing_Allocation model)
        {
            return _baseRepository.InsertReturnID<AntiFleeing_Allocation>(model);
        }
        /// <summary>
        /// 根据ID得到model
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AntiFleeing_Allocation AllocationGetModel(string id)
        {
            string sql = " SELECT AO.*,PP.PRODUCTNAME FROM ANTIFLEEING_ALLOCATION AO LEFT JOIN PRODUCT_PRODUCT PP ON PP.ID=AO.PRODUCTID WHERE AO.ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<AntiFleeing_Allocation>(sql, parameters);
        }
        /// <summary>
        /// 根据FormCode得到一个model
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public AntiFleeing_Allocation AlloGetModelByCode(string code)
        {
            string sql = @"SELECT  * FROM ANTIFLEEING_ALLOCATION WHERE ViewDel=0 and FORMCODE LIKE '" + code + "%'  ORDER BY CREATEDATE DESC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_Allocation>(sql, null);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AllocationUpdate(AntiFleeing_Allocation model)
        {
            return _baseRepository.Update<AntiFleeing_Allocation>(model);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public  PagedList<V_Allocation> AlloGetPageList(ref PagerModel pager, SearchModel SearchModel)
        {
            DataTable result = new DataTable();
            pager.FldName = @" * ";
            pager.TblName = @" V_Allocation as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "  1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }
            if (SearchModel != null)
            {
                if (!string.IsNullOrEmpty(SearchModel.Key))
                {
                    var list = GetParentCode(SearchModel.Key);
                    if (list != null)
                    {
                        SearchModel.Key = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_allocationdetails WHERE wlcode='" + SearchModel.Key + "')) ", SearchModel.Key);
                }
                if (!string.IsNullOrEmpty(SearchModel.Code))
                {
                    var list = GetParentCode(SearchModel.Code);
                    if (list != null)
                    {
                        SearchModel.Code = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_allocationdetails WHERE wlcode='" + SearchModel.Code + "')) ", SearchModel.Code);
                }
                if (!string.IsNullOrEmpty(SearchModel.ProductName))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.ProductName) like '%{0}%')", SearchModel.ProductName);
                }
                if (!string.IsNullOrEmpty(SearchModel.FromWarehouseName))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.FromWarehouseName) like '%{0}%')", SearchModel.FromWarehouseName);
                }
                if (!string.IsNullOrEmpty(SearchModel.GoWarehouseName))
                {
                    StrCondition += string.Format("  and (concat_ws('',w.GoWarehouseName) like '%{0}%')", SearchModel.GoWarehouseName);
                }
                if (!string.IsNullOrEmpty(SearchModel.StartDate))
                {
                    StrCondition += " and  (left(CreateDate,10) >= '" + SearchModel.StartDate + "')";
                }
                if (!string.IsNullOrEmpty(SearchModel.EndDate))
                {
                    StrCondition += " and  (left(CreateDate,10) <= '" + SearchModel.EndDate + "')";
                }
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = "   w.CreateDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_Allocation> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_Allocation>("Common_PageList", parameters);
            return new PagedList<V_Allocation>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 调拨编码
        /// </summary>
        /// <param name="id">机构ID</param>
        /// <returns></returns>
        public string AllocationGetCode(string id)
        {
            string Code = GetEnterprise(id).Code;
            if (Code.Length < 4)//如果小于4 用0补充
            {
                for (int i = 0; i < 4 - Code.Length; i++)
                {
                    Code += "0";
                }
            }
            //  else Code = Code.Substring(0, 4);
            //判断单据号是否存在
            AntiFleeing_Allocation model = AlloGetModelByCode("DB" + Code + "0001");
            string str = "";
            if (model == null)//不存在 直接加1
            {
                //  Code += "0001";
                return "DB" + Code + "0001";
            }
            else
            {
                str = model.FormCode.Substring(model.FormCode.Length - 4, 4);
                int num = int.Parse(str);
                num++;
                str = num.ToString();
                if (str.Length < 4)//如果小于4 用0补充
                {
                    str = str.PadLeft(4, '0');
                }
                else str = str.Substring(0, 4);
            }
            Code = "DB" + Code + str;
            return Code;
        }
        /// <summary>
        ///  填写物流码
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int AllocationBatchUpdate(string formCode, int warehouseId, int fromwarehouseId)
        {
            string sql = "UPDATE AntiFleeing_WareHousingDetails SET  AntiFleeing_WareHousingDetails.WarehouseId=@WarehouseId,AntiFleeing_WareHousingDetails.ChangeDate=@ChangeDate where WLcode in (select WLcode from antifleeing_allocationdetails where FormCode=@FormCode) and WarehouseId=@fromWarehouseId";
            MySqlParameter[] paras = 
                        {
                        new MySqlParameter("@WarehouseId",warehouseId),
                        new MySqlParameter("@FormCode",formCode),
                         new MySqlParameter("@fromWarehouseId",fromwarehouseId),
                          new MySqlParameter("@ChangeDate",DateTime.Now),//新添加
                        };
            return _baseRepository.ExecuteText<int>(sql, paras);
        }
        #endregion

        #region 产品退货
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ProReturnAdd(AntiFleeing_ProductReturn model)
        {
            return _baseRepository.InsertReturnID<AntiFleeing_ProductReturn>(model);
        }

        /// <summary>
        /// 根据ID得到model
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AntiFleeing_ProductReturn ProReturnGetModel(string id)
        {
            string sql = " SELECT AO.ID,AO.FORMCODE,AO.FILEPATH,AO.NUM,AO.BRANDID,AO.PRODUCTID,AO.REMARK,PP.PRODUCTNAME,AO.DistributorId FROM ANTIFLEEING_PRODUCTRETURN AO LEFT JOIN PRODUCT_PRODUCT PP ON PP.ID=AO.PRODUCTID WHERE AO.ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<AntiFleeing_ProductReturn>(sql, parameters);
        }
        public V_ProductReturn ProReturnShowData(string id)
        {
            string sql = " SELECT * FROM V_PRODUCTRETURN WHERE ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<V_ProductReturn>(sql, parameters);
        }
        /// <summary>
        /// 根据FormCode得到一个model
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public AntiFleeing_ProductReturn ReGetModelByCode(string code)
        {
            string sql = @"SELECT  * FROM ANTIFLEEING_PRODUCTRETURN WHERE ViewDel=0 and FORMCODE LIKE '" + code + "%'  ORDER BY CREATEDATE DESC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_ProductReturn>(sql, null);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ProReturnUpdate(AntiFleeing_ProductReturn model)
        {
            return _baseRepository.Update<AntiFleeing_ProductReturn>(model);
        }
        /// <summary>
        /// 根据表单编号修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ProReturnEdit(AntiFleeing_ProductReturn model)
        {
            string sql = "UPDATE ANTIFLEEING_PRODUCTRETURN SET GODISTRIBUTOR=@GODISTRIBUTOR,GOWAREHOUSEID=@GOWAREHOUSEID,CHECKSTATUS=@CHECKSTATUS WHERE FORMCODE=@FORMCODE";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@GODISTRIBUTOR",model.GoDistributor),
                             new MySqlParameter("@FORMCODE",model.FormCode),
                             new MySqlParameter("@CHECKSTATUS",model.CheckStatus),
                             new MySqlParameter("@GOWAREHOUSEID",model.GoWarehouseId),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public PagedList<V_ProductReturn> ReturnGetPageList(ref PagerModel pager, SearchModel SearchModel)
        {
            DataTable result = new DataTable();
            pager.FldName = @" * ";
            pager.TblName = @" V_ProductReturn as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "  1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }
            if (SearchModel != null)
            {
                if (!string.IsNullOrEmpty(SearchModel.Key))
                {
                    var list = GetParentCode(SearchModel.Key);
                    if (list != null)
                    {
                        SearchModel.Key = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_returndetails WHERE wlcode='" + SearchModel.Key + "')) ", SearchModel.Key);
                }
                if (!string.IsNullOrEmpty(SearchModel.Code))
                {
                    var list = GetParentCode(SearchModel.Code);
                    if (list != null)
                    {
                        SearchModel.Code = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_returndetails WHERE wlcode='" + SearchModel.Code + "')) ", SearchModel.Code);
                }
                if (!string.IsNullOrEmpty(SearchModel.ProductName))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.ProductName) like '%{0}%')", SearchModel.ProductName);
                }
                if (!string.IsNullOrEmpty(SearchModel.CheckStatus))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.CheckText) like '%{0}%')", SearchModel.CheckStatus);
                }
                if (!string.IsNullOrEmpty(SearchModel.ImportStatus))
                {
                    StrCondition += string.Format("  and (concat_ws('',w.statusText) like '%{0}%')", SearchModel.ImportStatus);
                }
                if (!string.IsNullOrEmpty(SearchModel.StartDate))
                {
                    StrCondition += " and  (left(CreateDate,10) >= '" + SearchModel.StartDate + "')";
                }
                if (!string.IsNullOrEmpty(SearchModel.EndDate))
                {
                    StrCondition += " and  (left(CreateDate,10) <= '" + SearchModel.EndDate + "')";
                }
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = " w.CreateDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_ProductReturn> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_ProductReturn>("Common_PageList", parameters);
            return new PagedList<V_ProductReturn>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 退货编码
        /// </summary>
        /// <param name="id">机构ID</param>
        /// <returns></returns>
        public string ProReturnGetCode(string id)
        {

            string Code = GetEnterprise(id).Code;
            if (Code.Length < 4)//如果小于4 用0补充
            {
                for (int i = 0; i < 4 - Code.Length; i++)
                {
                    Code += "0";
                }
            }
            //  else Code = Code.Substring(0, 4);

            //判断单据号是否存在
            AntiFleeing_ProductReturn model = ReGetModelByCode("TH" + Code + "0001");
            string str = "";
            if (model == null)//不存在 直接加1
            {
                // Code += "0001";
                return "TH" + Code + "0001";
            }
            else
            {

                str = model.FormCode.Substring(model.FormCode.Length - 4, 4);
                int num = int.Parse(str);
                num++;
                str = num.ToString();
                if (str.Length < 4)//如果小于4 用0补充
                {

                    str = str.PadLeft(4, '0');
                    //str= str.PadLeft(4-str.Length,'0');
                }
                else str = str.Substring(0, 4);

            }
            Code = "TH" + Code + str;
            return Code;
        }
        public int ProReturnCansole(string formCode)
        {
            string sql = "UPDATE antifleeing_productreturn set `CheckStatus`=4 where FormCode=@FormCode;UPDATE antifleeing_processingtype set `Status`=4 where FormCode=@FormCode;";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@FormCode",formCode),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }
        #endregion

        #region 产品调货
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int TranAdd(AntiFleeing_Transshipment model)
        {
            return _baseRepository.InsertReturnID<AntiFleeing_Transshipment>(model);
        }
        /// <summary>
        /// 根据ID得到model
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AntiFleeing_Transshipment TranGetModel(string id)
        {
            string sql = " SELECT AO.*,PP.PRODUCTNAME FROM ANTIFLEEING_TRANSSHIPMENT AO LEFT JOIN PRODUCT_PRODUCT PP ON PP.ID=AO.PRODUCTID WHERE AO.ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<AntiFleeing_Transshipment>(sql, parameters);
        }
        /// <summary>
        /// 根据FormCode得到一个model
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public AntiFleeing_Transshipment TranGetModelByCode(string code)
        {
            string sql = @"SELECT * FROM ANTIFLEEING_TRANSSHIPMENT WHERE  ViewDel=0 and FORMCODE LIKE '" + code + "%'  ORDER BY CREATEDATE DESC LIMIT 1";
            return _baseRepository.ExecuteText<AntiFleeing_Transshipment>(sql, null);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int TranUpdate(AntiFleeing_Transshipment model)
        {
            return _baseRepository.Update<AntiFleeing_Transshipment>(model);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public PagedList<V_Transshipment> TranGetPageList(ref PagerModel pager, SearchModel SearchModel)
        {
            DataTable result = new DataTable();

            pager.FldName = @" * ";
            pager.TblName = @" V_Transshipment as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "   1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }
            if (SearchModel != null)
            {
                if (!string.IsNullOrEmpty(SearchModel.Key))
                {
                    var list = GetParentCode(SearchModel.Key);
                    if (list != null)
                    {
                        SearchModel.Key = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_transdetails WHERE wlcode='" + SearchModel.Key + "')) ", SearchModel.Key);
                }
                if (!string.IsNullOrEmpty(SearchModel.Code))
                {
                    var list = GetParentCode(SearchModel.Code);
                    if (list != null)
                    {
                        SearchModel.Code = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_transdetails WHERE wlcode='" + SearchModel.Code + "')) ", SearchModel.Code);
                }
                if (!string.IsNullOrEmpty(SearchModel.ProductName))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.ProductName) like '%{0}%')", SearchModel.ProductName);
                }
                if (!string.IsNullOrEmpty(SearchModel.CheckStatus))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.CheckText) like '%{0}%')", SearchModel.CheckStatus);
                }
                if (!string.IsNullOrEmpty(SearchModel.ImportStatus))
                {
                    StrCondition += string.Format("  and (concat_ws('',w.statusText) like '%{0}%')", SearchModel.ImportStatus);
                }
                if (!string.IsNullOrEmpty(SearchModel.StartDate))
                {
                    StrCondition += " and  (left(CreateDate,10) >= '" + SearchModel.StartDate + "')";
                }
                if (!string.IsNullOrEmpty(SearchModel.EndDate))
                {
                    StrCondition += " and  (left(CreateDate,10) <= '" + SearchModel.EndDate + "')";
                }
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = "  w.CreateDate desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);

            IEnumerable<V_Transshipment> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_Transshipment>("Common_PageList", parameters);
            return new PagedList<V_Transshipment>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 退货编码
        /// </summary>
        /// <param name="id">机构ID</param>
        /// <returns></returns>
        public string TranGetCode(string id)
        {
            string Code = GetEnterprise(id).Code;
            if (Code.Length < 4)//如果小于4 用0补充
            {
                for (int i = 0; i < 4 - Code.Length; i++)
                {
                    Code += "0";
                }
            }
            //  else Code = Code.Substring(0, 4);
            //判断单据号是否存在
            AntiFleeing_Transshipment model = TranGetModelByCode("DH" + Code + "0001");
            string str = "";
            if (model == null)//不存在 直接加1
            {
                //  Code += "0001";
                return "DH" + Code + "0001";
            }
            else
            {
                str = model.FormCode.Substring(model.FormCode.Length - 4, 4);
                int num = int.Parse(str);
                num++;
                str = num.ToString();
                if (str.Length < 4)//如果小于4 用0补充
                {
                    str = str.PadLeft(4, '0');
                    //str= str.PadLeft(4-str.Length,'0');
                }
                else str = str.Substring(0, 4);
            }
            Code = "DH" + Code + str;
            return Code;
        }
        public int TranCansole(string formCode)
        {
            string sql = "UPDATE AntiFleeing_Transshipment set `CheckStatus`=4 where FormCode=@FormCode;UPDATE antifleeing_processingtype set `Status`=4 where FormCode=@FormCode;";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@FormCode",formCode),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }
        #endregion

        #region 处理状态
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ProcTypeAdd(AntiFleeing_ProcessingType model)
        {
            return _baseRepository.InsertReturnID<AntiFleeing_ProcessingType>(model);
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int ProcTypeUpdate(AntiFleeing_ProcessingType model)
        {
            return _baseRepository.Update<AntiFleeing_ProcessingType>(model);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public PagedList<V_ProcessingType> ProcTypeGetPageList(ref PagerModel pager, string key)
        {
            DataTable result = new DataTable();
            pager.FldName = @" * ";
            pager.TblName = @" V_ProcessingType as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "  1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }

            if (key != null && key != "")
            {
                StrCondition += " and  (w.ProductName like '%" + key + "%')";
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", ""))) pager.FldSort = "  w.CreateDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_ProcessingType> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_ProcessingType>("Common_PageList", parameters);
            return new PagedList<V_ProcessingType>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 得到实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AntiFleeing_ProcessingType THProcTypeModel(string formCode)
        {
            string sql = @"select m.*,pp.ProductName,LevelName,SaleAreaName,DistributorName from AntiFleeing_ProductReturn m
 left join Product_Product pp on pp.ID=m.ProductID 
  left join
  (select ad.ID,adi.LevelName,asa.Name as SaleAreaName,ad.Name as DistributorName from AntiFleeing_Distributor ad 
  left join AntiFleeing_DistributorLevel adi on ad.LevelID=adi.ID
  left join AntiFleeing_SalesArea asa on asa.ID=ad.SalesAreaID ) w on w.ID=m.DistributorId
  where  m.FormCode=@FormCode";
            MySqlParameter[] parameter = { new MySqlParameter("@FormCode", formCode) };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_ProcessingType>(sql, parameter).FirstOrDefault();
        }
        public AntiFleeing_ProcessingType DHProcTypeModel(string FromCode)
        {
            string sql = @"select m.*,pp.ProductName,w.* from AntiFleeing_Transshipment m
 left join Product_Product pp on pp.ID=m.ProductID 
  left join 
  (select ad.ID,adi.LevelName,asa.Name as SaleAreaName,ad.Name as DistributorName from AntiFleeing_Distributor ad 
  left join AntiFleeing_DistributorLevel adi on ad.LevelID=adi.ID
  left join AntiFleeing_SalesArea asa on asa.ID=ad.SalesAreaID ) w on w.ID=m.FromDistributor
  where  m.FormCode=@FormCode";
            MySqlParameter[] parameter = { new MySqlParameter("@FormCode", FromCode) };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_ProcessingType>(sql, parameter).FirstOrDefault();
        }
        public int ProcTypeUpdateStatus(int status, int id)
        {
            string sql = @"  update AntiFleeing_ProcessingType set Status=@Status  where ID=@ID;";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@Status",status),
                          //   new MySqlParameter("@FormCode",formCode),
                             new MySqlParameter("@ID",id),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }
        // 编辑收货经销商
        public int EditDistributor(string formCode, int goDisId, int goSaleAreaId, int status)
        {
            string sql = @"  UPDATE ANTIFLEEING_TRANSSHIPMENT SET GODISTRIBUTOR=@GODISTRIBUTOR ,GOSALEAREAID=@GOSALEAREAID,CHECKSTATUS=@CHECKSTATUS WHERE FORMCODE=@FORMCODE";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@GODISTRIBUTOR",goDisId),
                             new MySqlParameter("@GOSALEAREAID",goSaleAreaId),
                             new MySqlParameter("@FORMCODE",formCode),
                             new MySqlParameter("@CHECKSTATUS",status),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }
        public int UpdateStatus(int status, string formCode, string tableName, int checkStatus, int id)
        {
            string sql = @"  update AntiFleeing_ProcessingType set `Status`=@Status  where ID=@ID;  update " + tableName + " set CheckStatus=@CheckStatus where FormCode=@FormCode";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@Status",status),
                             new MySqlParameter("@FormCode",formCode),
                             new MySqlParameter("@CheckStatus",checkStatus),
                             new MySqlParameter("@ID",id),
                             };
            return _baseRepository.ExecuteSql<int>(sql, parameters);
        }
        #endregion

        #region 产品出库
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="key"></param>
        /// <param name="userId"></param>
        /// <param name="enterId"></param>
        /// <returns></returns>
        public PagedList<V_OutWarehouse> OutGetPageList(ref PagerModel pager, SearchModel SearchModel)
        {
            DataTable result = new DataTable();
            pager.FldName = @" * ";
            pager.TblName = @" V_OutWarehouse as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "  1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }
            if (SearchModel != null)
            {
                if (!string.IsNullOrEmpty(SearchModel.Key))
                {
                    var list = GetParentCode(SearchModel.Key);
                    if (list != null)
                    {
                        SearchModel.Key = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_outbounddetails WHERE wlcode='" + SearchModel.Key + "')) ", SearchModel.Key);
                }
                if (!string.IsNullOrEmpty(SearchModel.Code))
                {
                    var list = GetParentCode(SearchModel.Code);
                    if (list != null)
                    {
                        SearchModel.Code = list[0].Wl_Code;
                        pager.FldName += ",'" + list[0].levv + "' as Levv ";
                    }
                    StrCondition += string.Format(" and  (w.FormCode in(select DISTINCT formCode from antifleeing_outbounddetails WHERE wlcode='" + SearchModel.Code + "')) ", SearchModel.Code);
                }
                if (!string.IsNullOrEmpty(SearchModel.ProductName))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.ProductName) like '%{0}%')", SearchModel.ProductName);
                }
                if (!string.IsNullOrEmpty(SearchModel.ProductCode))
                {
                    StrCondition += string.Format(" and (concat_ws('',w.ProductCode) like '%{0}%')", SearchModel.ProductCode);
                }
                if (!string.IsNullOrEmpty(SearchModel.ImportStatus))
                {
                    StrCondition += string.Format("  and (concat_ws('',w.statusText) like '%{0}%')", SearchModel.ImportStatus);
                }
                if (!string.IsNullOrEmpty(SearchModel.StartDate))
                {
                    StrCondition += " and  (left(CreateDate,10) >= '" + SearchModel.StartDate + "')";
                }
                if (!string.IsNullOrEmpty(SearchModel.EndDate))
                {
                    StrCondition += " and  (left(CreateDate,10) <= '" + SearchModel.EndDate + "')";
                }
            }
            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = "  w.CreateDate desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<V_OutWarehouse> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_OutWarehouse>("Common_PageList", parameters);
            return new PagedList<V_OutWarehouse>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }
        /// <summary>
        /// 出库编码
        /// </summary>
        /// <param name="id">机构ID</param>
        /// <returns></returns>
        public string OutGetCode(string id)
        {
            string Code = GetEnterprise(id).Code;
            if (Code.Length < 4)//如果小于4 用0补充
            {
                for (int i = 0; i < 4 - Code.Length; i++)
                {
                    Code += "0";
                }
            }
            //  else Code = Code.Substring(0, 4);

            //判断单据号是否存在
            AntiFleeing_IOWarehouse model = GetIOWarehouse("CK" + Code + "0001");
            string str = "";
            if (model == null)//不存在 直接加1
            {
                // Code += "0001";
                return "CK" + Code + "0001";
            }
            else
            {
                str = model.FormCode.Substring(model.FormCode.Length - 4, 4);
                int num = int.Parse(str);
                num++;
                str = num.ToString();
                if (str.Length < 4)//如果小于4 用0补充
                {
                    str = str.PadLeft(4, '0');
                    //str= str.PadLeft(4-str.Length,'0');
                }
                else str = str.Substring(0, 4);
            }
            Code = "CK" + Code + str;
            return Code;
        }
        public AntiFleeing_IOWarehouse GetWarehouseModel(int id)
        {
            string sql = "SELECT AO.*,PP.PRODUCTNAME FROM ANTIFLEEING_IOWAREHOUSE AO LEFT JOIN PRODUCT_PRODUCT PP ON PP.ID=AO.PRODUCTID WHERE AO.ID=" + id;
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_IOWarehouse>(sql).FirstOrDefault();
        }
        public V_OutWarehouse OutShowData(int id)
        {
            string sql = "	SELECT * FROM V_OUTWAREHOUSE WHERE ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",id),
                             };
            return _baseRepository.ExecuteSqlCollection<V_OutWarehouse>(sql, parameters).FirstOrDefault();
        }
        //根据具体出库编码得到model
        public AntiFleeing_IOWarehouse GetModelByFormCode(string formCode)
        {
            string sql = @" select  ai.*,pp.ProductName,pp.EnterpriseName,ass.Name as SaleAreaName from AntiFleeing_IOWarehouse ai 
      left join
     ( select pp.ID,pp.ProductName,oe.EnterpriseName from Product_Product pp
      left join Organization_Enterprise oe on oe.ID=pp.EnterpriseId)pp on pp.ID=ai.ProductID
      left join AntiFleeing_SalesArea ass on ass.ID=ai.SaleAreaId
  where ai.FormCode=@FormCode";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@FormCode",formCode),       
                             };
            return _baseRepository.ExecuteText<AntiFleeing_IOWarehouse>(sql, parameters);
        }

        #endregion

        #region 窜货验证
        //// 根据物流码查询出库明细
        public AntiFleeing_OutboundDetails GetTableByCode(string WLcode)
        {
            string sql = @"select * from AntiFleeing_OutboundDetails  where WLcode='" + WLcode + "' order by ID desc limit 1";
            return _baseRepository.ExecuteText<AntiFleeing_OutboundDetails>(sql);
        }
        //根据单据编码查询出库明细
        public AntiFleeing_OutboundDetails GetTableByFormcode(string FormCode, string code)
        {
            string sql = @"select * from AntiFleeing_OutboundDetails  where FormCode='" + FormCode + "' and WLcode='" + code + "' order by ID desc limit 1";
            return _baseRepository.ExecuteText<AntiFleeing_OutboundDetails>(sql);
        }
        // //根据物流码查询上级出库信息
        public AntiFleeing_OutboundDetails GetParentTableByCode(string WLcode)
        {
            try
            {
                string tableName = "nbcodemain.AntiFake_Digitcode" + WLcode.Substring(0, 6);
                string sql = @"  select * from AntiFleeing_OutboundDetails  where WLcode='" + WLcode + "' order by ID desc limit 1";
                return _baseRepository.ExecuteText<AntiFleeing_OutboundDetails>(sql);
            }
            catch (Exception)
            {
                return null;
            }
        }
        public AntiFleeing_Distributor GetTableById(int Id)
        {
            string sql = @"select  ad.*,oe.EnterpriseName,adl.LevelName as DisLevelName,asa.Name as SalesArea,a.name as ProvinceName,
  b.name as CityName ,c.name as RegionName
   from AntiFleeing_Distributor ad
   left join Organization_Enterprise oe on ad.EnterpriseId=oe.ID
   left join AntiFleeing_DistributorLevel adl on adl.ID=ad.LevelID
   left join AntiFleeing_SalesArea asa on asa.ID=ad.SalesAreaID 
   left join Sys_Region a on a.codeID=ad.Province
    left join Sys_Region b on b.codeID=ad.City
      left join Sys_Region c on c.codeID=ad.Region
   where ad.ID=" + Id + " limit 1";
            return _baseRepository.ExecuteText<AntiFleeing_Distributor>(sql);
        }
        #endregion

        #region 窜货记录

        //分离单标、拆箱记录
        public PagedList<AntiFleeing_DevanningRecords> RecordList(ref PagerModel pager, string key)
        {
            DataTable result = new DataTable();
            pager.FldName = @" * ";
            pager.TblName = @" (select a.*,RealName as CreateByName,(case a.type when 1 then '拆箱' when 2 then '分离单标'  else '装箱/补箱' END ) as TypeText,
(case a.type when 1 then '下级码' when 2 then '上级码'  END ) as codeType from AntiFleeing_DevanningRecords a left join sys_user b on a.createby=b.id
) as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = "   1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }

            if (key != null && key != "")
            {
                StrCondition += " and  (w.CZCode like '%" + key + "%')";
            }
            pager.StrCondition = StrCondition;
            pager.FldSort = "  w.CreateOn desc ";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<AntiFleeing_DevanningRecords> resultList = _baseRepository.ExecuteStoredProcedureCollection<AntiFleeing_DevanningRecords>("Common_PageList", parameters);
            return new PagedList<AntiFleeing_DevanningRecords>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        public int RecordAdd(AntiFleeing_DevanningRecords model)
        {
            return _baseRepository.Insert<AntiFleeing_DevanningRecords>(model);
        }
        //出库记录

        public List<AntiFleeing_OutRecords> OutRecordList(string key, string parentCode)
        {
            string sql = "select DISTINCT o.*,i.distributor,i.CreateloginName from antifleeing_outbounddetails o " +
" LEFT JOIN  (SELECT * from  antifleeing_iowarehouse where formtype=2) i on o.FormCode=i.FormCode " +
" where o.WLcode='" + key + "' or o.WLcode='" + parentCode + "'";
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_OutRecords>(sql).ToList();
        }
        //电子履历
        public List<Antifleeing_OurLife> OurLife(string key, string pCode, string pName)
        {
            string sql = "select DISTINCT '" + key + "' as WLcode, m.*,IFNULL(RealName,m.CreateLoginName) as `Name`, IFNULL(u.levelName,'企业')as levelName,'" + pName + "' as productName,u.AreaName,aw.WarehouseName ,StatusText " +
 " from  ( " +
 " select StatusText,IFNULL(o.createDate,w.CreateDate)as CreateDate,w.CreateLoginID,w.CreateLoginName,'出库' as text,IFNULL(o.Remark,w.Remark)as Remark,w.WarehouseId,dis.`Name` as Receiver  " +
 " from ( " +
 " select FormCode,'操作成功' AS StatusText,CreateDate,null as Remark from antifleeing_outbounddetails where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " UNION ALL  " +
 " select FormCode,'操作失败' AS StatusText,NULL as CreateDate,Remark from antifleeing_error_outbounddetails where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " ) o LEFT JOIN antifleeing_iowarehouse w  on o.FormCode=w.formcode LEFT JOIN antifleeing_distributor dis on w.distributor=dis.ID " +
 " UNION ALL " +
 "  select StatusText,IFNULL(r.createDate,p.CreateDate)as CreateDate,p.CreateLoginID,p.CreateLoginName,'退货' as text,IFNULL(r.Remark,p.Remark)as Remark,0 as WarehouseId,IFNULL(dis.`Name`,'企业') as Receiver   " +
 " from ( " +
 " select FormCode,'操作成功' AS StatusText,CreateDate,null as Remark from antifleeing_returndetails  where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " UNION ALL  " +
 " select FormCode,'操作失败' AS StatusText,NULL as CreateDate,Remark from antifleeing_error_returndetails where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " )r left join antifleeing_productreturn p on r.FormCode=p.formcode LEFT JOIN antifleeing_distributor dis on p.GoDistributor=dis.ID " +
 " UNION ALL  " +
 " select StatusText,IFNULL(t.createDate,tt.CreateDate)as CreateDate,tt.CreateLoginID,tt.CreateLoginName,'调货' as text,IFNULL(t.Remark,tt.Remark)as Remark,0 as WarehouseId,dis.`Name` as Receiver     " +
 " from ( " +
 " select FormCode,'操作成功' AS StatusText,CreateDate,null as Remark from antifleeing_transdetails  where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " UNION ALL " +
 " select FormCode,'操作失败' AS StatusText,NULL as CreateDate,Remark from antifleeing_error_transdetails where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " )t left join antifleeing_transshipment tt on t.FormCode=tt.formcode LEFT JOIN antifleeing_distributor dis on tt.GoDistributor=dis.ID" +
 "  UNION ALL  " +
 " select StatusText,IFNULL(ww.createDate,w.CreateDate)as CreateDate,w.CreateLoginID,w.CreateLoginName,'入库' as text,IFNULL(ww.Remark,w.Remark)as Remark,IF(ww.WarehouseId=0,w.WarehouseId,ww.WarehouseId)as WarehouseId,'' as Receiver  " +
 " from ( " +
 " select FormCode,'操作成功' AS StatusText,CreateDate,WarehouseId,null as Remark from antifleeing_warehousingdetails  where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " UNION ALL " +
 " select FormCode,'操作失败' AS StatusText,NULL as CreateDate,0 as WarehouseId,Remark from antifleeing_error_warehousingdetails where WLcode='" + key + "' or WLcode='" + pCode + "' " +
 " )ww   left join antifleeing_iowarehouse w on ww.FormCode=w.formcode " +
 "  UNION ALL " +
 "  select StatusText,IFNULL(ad.createDate,a.CreateDate)as CreateDate,a.CreateLoginID,a.CreateLoginName,'调拨' as text,IFNULL(ad.Remark,a.Remark)as Remark,a.FromWarehouseId as WarehouseId ,wh.WarehouseName as Receiver" +
 " from ( " +
 " select FormCode,'操作成功' AS StatusText,CreateDate,null as Remark from antifleeing_allocationdetails  where WLcode='" + key + "' or WLcode='" + pCode + "' UNION ALL select FormCode,'操作失败' AS StatusText,NULL as CreateDate, Remark from antifleeing_error_allocationdetails where WLcode='0613125221410599' or WLcode='0613125221410599')ad   left join antifleeing_allocation a on ad.FormCode=a.formcode left JOIN antifleeing_warehouse wh on a.GoWarehouseId=wh.id " +
 " UNION ALL  " +
 " select '操作成功' as StatusText,CreateOn as createDate,createby as CreateLoginID,'' as CreateLoginName,(case type when 1 then '拆箱' when 2 then '分离单标' when 4 then '出库拆箱'  ELSE'装箱/补箱' END ) as Text,'' as Remark," +
 " 0 as WarehouseId,'' as Receiver  from AntiFleeing_DevanningRecords  where CZCode='" + key + "'" +
 " ) m " +
 "  LEFT JOIN sys_user on m.CreateLoginID=sys_user.ID  " +
 " left join  " +
 " (  select a.id,b.levelName,sa.`Name` as AreaName from antifleeing_distributor a " +
 " left join antifleeing_distributorlevel b  on a.levelId=b.id " +
 " left join antifleeing_salesarea sa on a.SalesAreaID=sa.ID ) u on sys_user.Distributor=u.id " +
 "  left join antifleeing_warehouse aw on aw.ID=m.WarehouseId order by createDate ASC";
            return _baseRepository.ExecuteSqlCollection<Antifleeing_OurLife>(sql).ToList();
        }
        public string getProductName(string code)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);
            string sql = @"select productName from product_product where ID=(select ProductId from {0} where Wl_Code='{1}')";
            sql = string.Format(sql, tableName, code);

            var result = _baseRepository.GetSingle(sql);
            if (result == null)
            {
                return " ";
            }
            return result.ToString();
        }
        #endregion

        #region 窜货统计
        public int YstdBoundByDis(int userId, int formType, string sTime, string eTime)
        {
            string sql = "SELECT SUM(DragNum)AS CountNum FROM `ANTIFLEEING_IOWAREHOUSE` WHERE FORMTYPE={3} AND `STATUS`=2 AND CREATELOGINID={0} AND CREATEDATE BETWEEN '{1}' AND '{2}' ";
            sql = string.Format(sql, userId, sTime, eTime, formType);
            var model = _baseRepository.ExecuteText<AntiFleeing_IOWarehouse>(sql);
            return int.Parse(model.CountNum == null ? "0" : model.CountNum.ToString());
        }
        public int YstdBoundByEnt(int enterId, int formType, string sTime, string eTime)
        {
            string sql = "SELECT SUM(DragNum)AS CountNum FROM `ANTIFLEEING_IOWAREHOUSE` WHERE FORMTYPE={3} AND `STATUS`=2 AND CREATELOGINID IN(SELECT ID FROM SYS_USER WHERE ENTERPRISEID={0} AND DISTRIBUTOR=0) AND CREATEDATE BETWEEN '{1}' AND '{2}' ";
            sql = string.Format(sql, enterId, sTime, eTime, formType);
            var model = _baseRepository.ExecuteText<AntiFleeing_IOWarehouse>(sql);
            return int.Parse(model.CountNum==null?"0":model.CountNum.ToString());
        }
        #endregion

        #region 拆箱
        public int SlipCase(string code)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);
            string sql = @"update {0} set ParentCode='0' where Wl_Code in (select * from (select Wl_Code from {0} where ParentCode='{1}') a)";
            sql = string.Format(sql, tableName, code);
            return _baseRepository.ExecuteText<int>(sql);
        }
        public int AddDevanningRecords(AntiFleeing_DevanningRecords model)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + model.CZCode.Substring(0, AppConst.SubStrLen);//获取到表名
            string sql = @"insert into AntiFleeing_DevanningRecords(CZCode,CreateOn,CreateBy,type,GLCode)
select '{0}' as CZCode,'{1}' as CreateOn,'{2}' as CreateBy, {3} as type,Wl_Code as GLCode from {4} where ParentCode='{0}'";
            sql = string.Format(sql, model.CZCode, model.CreateOn, model.CreateBy, model.Type, tableName);
            return _baseRepository.ExecuteSql<int>(sql);
        }

        //拆箱时批量插入各种操作记录
        public int AddRecordsforSplit(string code)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);
            string sql = @"-- 调拨
insert into antifleeing_allocationdetails(WLcode,FormCode,createDate,CreateLoginID,CreateLoginName) 
(select  b.WL_Code as WLcode,a.FormCode,a.createDate,a.CreateLoginID,a.CreateLoginName  from antifleeing_allocationdetails a 
left join {0} b on a.wlcode=b.parentCode 
where a.WLcode='{1}' and b.parentCode='{1}'  );

-- 出库
insert into antifleeing_outbounddetails(WLcode,formCode,distributorId,createDate,changeDate)
(select  b.WL_Code as WLcode,a.FormCode,a.distributorId,a.createDate,a.changeDate  from antifleeing_outbounddetails a 
left join {0} b on a.wlcode=b.parentCode 
where a.WLcode='{1}' and b.parentCode='{1}' );

-- 退货
insert into antifleeing_returndetails(WLcode ,FormCode,createDate,CreateLoginID)
(select  b.WL_Code as WLcode,a.FormCode,a.createDate,a.CreateLoginID  from antifleeing_returndetails a 
left join {0} b on a.wlcode=b.parentCode 
where a.WLcode='{1}' and b.parentCode='{1}' );

-- 调货
insert into antifleeing_transdetails (WLcode ,FormCode,createDate,CreateLoginID)
(select  b.WL_Code as WLcode,a.FormCode,a.createDate,a.CreateLoginID from antifleeing_transdetails a 
left join {0} b on a.wlcode=b.parentCode 
where a.WLcode='{1}' and b.parentCode='{1}');

-- 入库
insert into antifleeing_warehousingdetails(WLcode ,FormCode,UserID,WarehouseId ,createDate,changedate)
(select  b.WL_Code as WLcode,a.FormCode,a.UserID,a.WarehouseId,a.createDate,a.changedate  from antifleeing_warehousingdetails a 
left join {0} b on a.wlcode=b.parentCode 
where a.WLcode='{1}' and b.parentCode='{1}');
";
            sql = string.Format(sql, tableName, code);
            return _baseRepository.ExecuteSql<int>(sql);
        }
        #endregion

        //批量冻结
        public int FrozenOrThaw(string codes, int flag)
        {
            string tableName = "nbcodemain.AntiFake_Digitcode" + codes.Substring(0, AppConst.SubStrLen);
            string sql = "update {0} set flag={1} where WL_code in ({2})";
            return _baseRepository.ExecuteSql<int>(string.Format(sql, tableName, flag, codes));
        }

        public int AddErrorReturnPro(string str)
        {
            string sql = "insert into antifleeing_error_returndetails(FormCode,WlCode,Remark)value " + str;
            return _baseRepository.ExecuteSql<int>(sql);
        }
        public int AddErrorTranPro(string str)
        {
            string sql = "insert into antifleeing_error_transdetails(FormCode,WlCode,Remark)value " + str;
            return _baseRepository.ExecuteSql<int>(sql);
        }

        #region 微信小程序
        public IEnumerable<SelectItem> GetSaleAreaServiceApp(int dicId)
        {
            string sql = "SELECT ID,NAME FROM ANTIFLEEING_SALESAREA WHERE ID=(SELECT SALESAREAID FROM ANTIFLEEING_DISTRIBUTOR  WHERE ID=@ID)";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@ID",dicId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql, parameters).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.Name
                };
            });
        }
        public IEnumerable<SelectItem> GetSaleAreaServiceApp(string enterpriseId)
        {
            string sql = " SELECT ID,NAME FROM ANTIFLEEING_SALESAREA WHERE VIEWDEL=0 ";
            if (enterpriseId != "1")
            {
                sql += " " + "AND ENTERPRISEID IN ({0})";
                sql = string.Format(sql, enterpriseId);
            }
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.Name
                };
            });
        }
        public IEnumerable<SelectItem> GetDistributorServiceApp(int saleAreaId, int selfDisId)
        {
            string sql = "  SELECT ID,NAME FROM ANTIFLEEING_DISTRIBUTOR WHERE VIEWDEL=0 AND SALESAREAID=@SALESAREAID AND FIND_IN_SET(ID,getDiscChild(@ID)) And ID!=@DisId";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@SALESAREAID",saleAreaId),
                                            new MySqlParameter("@ID",selfDisId),
                                             new MySqlParameter("@DisId",selfDisId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql, parameters).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.Id,
                    Name = p.Name
                };
            });
        }
        public IEnumerable<SelectItem> GetDistributorServiceApp(int saleAreaId)
        {
            string sql = "  SELECT ID,NAME FROM ANTIFLEEING_DISTRIBUTOR WHERE VIEWDEL=0 AND SALESAREAID=" + saleAreaId;
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.Id,
                    Name = p.Name
                };
            });
        }
        public IEnumerable<SelectItem> GetWarehouseServiceApp(int userId)
        {
            string sql = "select ID,WarehouseName from AntiFleeing_Warehouse where ViewDel=0 and ChangeLoginID=@CreateLoginID ";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@CreateLoginID",userId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql, parameters).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }
        public IEnumerable<SelectItem> GetWarehouseServiceApp(string enterIds)
        {
            string sql = "select ID, WarehouseName from AntiFleeing_Warehouse where  ViewDel=0 and ChangeLoginID in(select ID from sys_user where Distributor=0 and EnterpriseId in ({0}))";
            if (enterIds != " getJuniorEnterprise(1)")
            {
                sql = string.Format(sql, enterIds);
            }
            else sql = string.Format(sql, 1);//管理员
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }
        public IEnumerable<SelectItem> GetWarehouseServiceApp(int userId, int wId)
        {
            string sql = "select ID,WarehouseName from AntiFleeing_Warehouse where ViewDel=0 and ID<>@ID  and ChangeLoginID=@CreateLoginID ";
            MySqlParameter[] parameters = {
                                            new MySqlParameter("@CreateLoginID",userId),
                                             new MySqlParameter("@ID",wId),
                                        };
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql, parameters).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }
        public IEnumerable<SelectItem> GetWarehouseServiceApp(string enterIds, int wId)
        {
            string sql = "select ID, WarehouseName from AntiFleeing_Warehouse where  ViewDel=0 and ID<>{1} and ChangeLoginID in(select ID from sys_user where Distributor=0 and EnterpriseId in ({0}))";
            if (enterIds != " getJuniorEnterprise(1)")
            {
                sql = string.Format(sql, enterIds, wId);
            }
            else sql = string.Format(sql, 1, wId);//管理员
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }
        public IEnumerable<SelectItem> GetProcductServiceApp(string enterIds, string product)
        {
            string sql = "SELECT ID,PRODUCTNAME FROM PRODUCT_PRODUCT WHERE VIEWDEL=0 ";
            if (enterIds != " getJuniorEnterprise(1)")
            {
                sql += " " + " AND ENTERPRISEID IN({0})";
                sql = string.Format(sql, enterIds);
            }

            if (!string.IsNullOrWhiteSpace(product))
            {
                sql = string.Format("SELECT ID,PRODUCTNAME FROM PRODUCT_PRODUCT WHERE VIEWDEL=0 And ID={0}", product);
            }

            return _baseRepository.ExecuteSqlCollection<Product_Product>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.Id,
                    Name = p.ProductName
                };
            });
        }
        public IEnumerable<SelectItem> GetWarehouseInfo(int id)
        {
            string sql = "SELECT ID ,WAREHOUSENAME FROM ANTIFLEEING_WAREHOUSE WHERE VIEWDEL=0 AND ID=" + id;

            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Warehouse>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.WarehouseName
                };
            });
        }

        /// <summary>
        /// 重载
        /// GetDigitcodeModel
        /// 0.1
        /// Lv
        /// </summary>
        public AntiFake_Digitcode GetDigitcodeModel(string _o_code, string _m_code, bool is_wl_code)
        {
            try
            {
                string _tb_name = string.Format("nbcodemain.AntiFake_Digitcode{0}", _m_code.Substring(0, AppConst.SubStrLen));
                string _where = string.Empty;
                if (is_wl_code)
                {
                    _where = string.Format(" Wl_Code = '{0}' LIMIT 1 ", _o_code);
                }
                else
                {
                    _where = string.Format(" Fw_Code = '{0}' LIMIT 1 ", _o_code);
                }

                string _sql = string.Format("SELECT * FROM {0} WHERE {1}", _tb_name, _where);
                return _baseRepository.ExecuteText<AntiFake_Digitcode>(_sql);
            }
            catch
            {
                return null;
            }
        }

        public AntiFake_Digitcode GetDigitcodeModel(string code)
        {
            try
            {
                string tabSuffix = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);//获取到表名
                string sql = @"select * from {0}  where Fw_Code='{1}' limit 1";
                sql = string.Format(sql, tabSuffix, code);
                return _baseRepository.ExecuteText<AntiFake_Digitcode>(sql);
            }
            catch (Exception)
            {

                return null;
            }
        }
        public List<AntiFake_Digitcode> GetDigitCodeList(string code)
        {
            string tabSuffix = "nbcodemain.AntiFake_Digitcode" + code.Substring(0, AppConst.SubStrLen);//获取到表名
            string sql = @"select * from {0}  where ParentCode='{1}'";
            return _baseRepository.ExecuteSqlCollection<AntiFake_Digitcode>(string.Format(sql,tabSuffix, code)).ToList();
        }
        #endregion



    }
}
